# @Author ZhangGJ
# @Date 2021/12/23 06:15
import pymysql
from pymysql.cursors import DictCursor


def sharding(filename, schema, schema_start, schema_end, table, table_start, table_end):
    conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='123456',
                           charset='utf8')
    cur = conn.cursor(DictCursor)
    with open(filename, encoding='utf-8', mode='r') as f:
        sql_list = f.read()
    sql = sql_list.split(';', 3)
    for i in range(schema_start, schema_end + 1):
        if i < 10:
            schema_name = '{}{}{}'.format(schema, 0, i)
        else:
            schema_name = '{}{}'.format(schema, i)
        cur.execute(sql[0].replace('#{schema_name}', schema_name))
        cur.execute(sql[1].replace('#{schema_name}', schema_name))
        print(f'Schema: {schema_name}============================================')
        for j in range(table_start, table_end + 1):
            table_name = '{}{}'.format(table, j)
            cur.execute(sql[2].replace('#{table_name}', table_name))
            cur.execute(sql[3].replace('#{table_name}', table_name))
            print(f'Table: {table_name}')
    cur.close()
    conn.commit()
    conn.close()


if __name__ == '__main__':
    # 参数
    # 文件路径
    # schema前缀
    # schema后面两位的起止数
    # table前缀
    # table后面两位的起止数
    sharding('test.sql', 'test2022', 1, 2, 'test', 0, 3)
